﻿using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;

namespace GoodAdmin.Core;

public class ExcelImportHelper
{
    /// <summary>
    /// 这个方法接受一个文件路径作为参数。
    /// 它使用FileStream打开文件，并使用WorkbookFactory.Create方法尝试创建IWorkbook对象。
    /// 如果成功创建了IWorkbook对象，说明文件是有效的Excel文件，返回true；否则，返回false。
    /// 使用文件后缀判断可能不准确。
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns></returns>
    public static bool IsExcel(string filePath)
    {
        using FileStream fileStream = new(filePath, FileMode.Open, FileAccess.Read);
        try
        {
            IWorkbook workbook = WorkbookFactory.Create(fileStream);
            return true;
        }
        catch (Exception)
        {
            return false;
        }
    }
    #region 读取Excel文件到DataTable
    
    /// <summary>
    /// 从Excel中获取数据到DataTable.
    /// </summary>
    /// <param name="filePath">Excel文件全路径(服务器路径).</param>
    /// <param name="SheetIndex">要获取数据的工作表序号(从0开始).</param>
    /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始).</param>
    /// <returns></returns>
    public static async Task<DataTable> ToDataTable(string filePath, int SheetIndex = 0, int HeaderRowIndex = 0)
    {
        IWorkbook workbook = null;
        try
        {
            using FileStream fileStream = new(filePath, FileMode.Open, FileAccess.Read);
            workbook = await Task.Run(() => WorkbookFactory.Create(fileStream));
            using FileStream workbookStream = new(filePath, FileMode.Open, FileAccess.Read);
            if (workbook is HSSFWorkbook)// Excel 97-2003版本
            {
                workbook = await Task.Run(() => new HSSFWorkbook(workbookStream));//异步加载工作簿 
            }
            else if (workbook is XSSFWorkbook)// Excel 2007及更高版本
            {
                workbook = await Task.Run(() => new XSSFWorkbook(workbookStream));//异步加载工作簿  
            }
        }
        catch (Exception)
        {
            throw Oops.Bah(FileErrorCode.F001);
        }        
        return await ToDataTable(workbook, SheetIndex, HeaderRowIndex);
    }

    /// <summary>
    /// 从Excel中获取数据到DataTable.
    /// </summary>
    /// <param name="workbook">要处理的工作薄.</param>
    /// <param name="SheetIndex">要获取数据的工作表序号(从0开始).</param>
    /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始).</param>
    /// <returns></returns>
    private static async Task<DataTable> ToDataTable(IWorkbook workbook, int SheetIndex, int HeaderRowIndex)
    {
        ISheet sheet = await Task.Run(() => workbook.GetSheetAt(SheetIndex));
        DataTable table = new();
        try
        {
            
            IRow headerRow =  await Task.Run(() => sheet.GetRow(HeaderRowIndex));
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new (headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            int rowCount = sheet.LastRowNum;
            if (rowCount > 0)
            {
                #region 循环各行各列,写入数据到DataTable
                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = await Task.Run(() => sheet.GetRow(i));
                    DataRow dataRow = table.NewRow();
                    bool isNullRow = true;
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = await Task.Run(() => row.GetCell(j));
                        if (cell == null)
                        {
                            dataRow[j] = null;
                        }
                        else
                        {
                            isNullRow = false;
                            if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
                            {
                                dataRow[j] = cell.DateCellValue?.ToString("yyyy/MM/dd");
                            }
                            else
                            {
                                dataRow[j] = cell.ToString().Trim();
                            }
                        }
                    }
                    if (!isNullRow)
                    {
                        table.Rows.Add(dataRow);
                    }
                    
                }
                #endregion
            }
        }
        catch (Exception ex)
        {
            table.Clear();
            table.Columns.Clear();
            table.Columns.Add("出错了");
            DataRow dr = table.NewRow();
            dr[0] = ex.Message;
            table.Rows.Add(dr);
            return table;
        }
        return table;
    }
    #endregion

    #region 读取Excel文件到List
    public static async Task<IWorkbook> ReadExcelWorkbook(string filePath)
    {
        using FileStream fileStream = new(filePath, FileMode.Open, FileAccess.Read);
        IWorkbook workbook = null;
        try
        {
            workbook = WorkbookFactory.Create(fileStream);
            using FileStream workbookStream = new(filePath, FileMode.Open, FileAccess.Read);
            if (workbook is HSSFWorkbook)// Excel 97-2003版本
            {
                workbook = await Task.Run(() => new HSSFWorkbook(workbookStream));//异步加载工作簿 
            }
            else if (workbook is XSSFWorkbook)// Excel 2007及更高版本
            {
                workbook = await Task.Run(() => new XSSFWorkbook(workbookStream));//异步加载工作簿  
            }
        }
        catch (Exception)
        {
            throw Oops.Bah(FileErrorCode.F001);
        }
        return workbook;
    }
    /// <summary>
    /// 从Excel中获取数据到List<Entity/>.
    /// </summary>
    /// <param name="filePath">Excel文件全路径(服务器路径).</param>
    /// <param name="fieldMapping">实体字段与excel表头对应关系{ "表头列1", "Property1" },</param>
    /// <param name="SheetIndex">要获取数据的工作表序号(从0开始).</param>
    /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始).</param>
    /// <param name="ContentRowIndex">工作表内容所在行号(从0开始).</param>
    /// <returns></returns>
    public static async Task<List<Entity>> ReadExcelToList<Entity>(string filePath, List<EntityMetadata> fieldMapping, int SheetIndex = 0, int HeaderRowIndex = 0,int ContentRowIndex=0)where Entity : class, new()
    {
        using FileStream fileStream = new(filePath, FileMode.Open, FileAccess.Read);
        IWorkbook workbook = await ReadExcelWorkbook(filePath);        
        return await ReadExcelToList<Entity>(workbook, fieldMapping,SheetIndex, HeaderRowIndex, ContentRowIndex);
    }
    private static async Task<List<Entity>> ReadExcelToList<Entity>(IWorkbook workbook, List<EntityMetadata> fieldMapping, int SheetIndex, int HeaderRowIndex, int ContentRowIndex) where Entity : class, new()
    {
        ISheet sheet = await Task.Run(() => workbook.GetSheetAt(SheetIndex));
        List<Entity> list= new ();
        try
        {
            IRow headerRow = await Task.Run(() => sheet.GetRow(HeaderRowIndex));
            int cellCount = headerRow.LastCellNum;

            // 根据表头行和字段映射字典创建列索引映射
            var columnMapping = new Dictionary<string, int>();
            for (int columnIndex = headerRow.FirstCellNum; columnIndex < headerRow.LastCellNum; columnIndex++)
            {
                var cell = headerRow.GetCell(columnIndex);
                string columnHeader = cell?.ToString();

                if (fieldMapping.Any(it=>it.Comment==columnHeader))
                {
                    columnMapping[fieldMapping.First(it=> it.Comment == columnHeader).FieldName] = columnIndex;
                }
            }

            int rowCount = sheet.LastRowNum;
            if (rowCount > 0)
            {
                #region 循环各行各列,写入数据到DataTable
                int firstRowNum = ContentRowIndex - sheet.FirstRowNum + 1;
                for (int i = firstRowNum; i <= sheet.LastRowNum; i++)
                {
                    var entity = new Entity();
                    IRow row = await Task.Run(() => sheet.GetRow(i));
                    if (row == null) continue;
                    bool isNullRow = true;
                    foreach (var item in columnMapping)
                    {
                        int columnIndex = item.Value;
                        string fieldName = item.Key;
                        var cell = row.GetCell(columnIndex);
                        object cellValue;
                        var field = typeof(Entity).GetProperty(fieldName);
                        if (cell == null)
                        {
                            cellValue = null;                            
                        }
                        else
                        {
                            isNullRow = false;
                            if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
                            {
                                cellValue = cell.DateCellValue;
                            }
                            else if (cell.CellType == CellType.Numeric)
                            {
                                var fieldType = field?.PropertyType;
                                if (fieldType == typeof(int))
                                {
                                    cellValue=cell.NumericCellValue.ToInt();
                                }
                                else if(fieldType == typeof(float)){
                                    cellValue = cell.NumericCellValue.ToDouble();
                                }
                                else if(fieldType == typeof(long))
                                {
                                    cellValue = cell.NumericCellValue.ToLong();
                                }
                                else if (fieldType == typeof(string))
                                {
                                    cellValue = cell.NumericCellValue.ToString();
                                }
                                else if(fieldType == typeof(decimal))
                                {
                                    cellValue = cell.NumericCellValue.ToDecimal();
                                }
                                else
                                {
                                    cellValue = cell.NumericCellValue;
                                }                               
                            }
                            else
                            {
                                cellValue = cell.StringCellValue.Trim();
                            }
                        }
                        // 根据实体字段名称设置对应的属性值
                        field?.SetValue(entity, cellValue);
                    }
                    // 如果当前行不是空行，则添加到列表
                    if (!isNullRow) 
                    { 
                        list.Add(entity); 
                    }
                        
                }
                #endregion
            }
        }
        catch (Exception)
        {
            throw;
        }
        return list;
    }
    #endregion
}